package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.health.pojo.CheckItem;
import com.itheima.health.pojo.Setmeal;
import com.itheima.health.vo.CheckGroupVO;
import com.itheima.health.vo.SetmealVO;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface SetmealMapper extends BaseMapper<Setmeal> {

    @Insert("insert into t_setmeal_checkgroup values(#{sid},#{checkgroupId})")
    void addSetmealAndGroupIdInfo(@Param("sid") Integer sid, @Param("checkgroupId") Integer checkgroupId);

    @Select("select CHECKGROUP_ID from t_setmeal_checkgroup where SETMEAL_ID=#{sid}")
    Integer[] findCheckgroupInfoBySetmealId(@Param("sid") Integer sid);

    @Delete("delete from t_setmeal_checkgroup where SETMEAL_ID=#{id}")
    Boolean deleteSetmealAndCheckgroupInfoBySetmealId(@Param("id") Integer id);

    @Select("select * from t_checkgroup")
    List<CheckGroupVO> selectCheckgroupInfo();

    @Select("SELECT `NAME`,REMARK,SEX,AGE,IMG " +
            "FROM t_setmeal " +
            "WHERE id=#{id}")
    SetmealVO selectSetmealInfoById(@Param("id") Integer id);

    @Select("SELECT tc.ID,tc.`NAME`,tc.REMARK " +
            "FROM t_setmeal_checkgroup tsc,t_checkgroup tc " +
            "WHERE tsc.CHECKGROUP_ID=tc.ID " +
            "and tsc.SETMEAL_ID=#{id}")
    List<CheckGroupVO> selectCheckgroupInfoById(@Param("id") Integer id);

    @Select("SELECT tc.`NAME` " +
            "FROM t_checkgroup_checkitem tcc,t_checkitem tc " +
            "WHERE tcc.CHECKITEM_ID=tc.ID " +
            "and tcc.CHECKGROUP_ID=#{id}")
    List<CheckItem> selectCheckitemInfoByGroupId(@Param("id") Integer id);

    //报表数据 todoby dzf
    @Select("select ts.NAME as name ,count(ts.id) as setmeal_count,count(ts.id)/(select count(1) from t_order) as proportion,ts.remark " +
            "from t_order tor,t_setmeal ts " +
            "where tor.setmeal_id = ts.id " +
            "group by ts.id order by setmeal_count desc ")
    List<Map> getHostSetmeal();

    //查询所有套餐信息
    @Select("select id,name,sex,remark,age,img from t_setmeal")
    List<Setmeal> findAllSetmeal();
}
